using Npgsql;
using System;
using System.Collections.ObjectModel;
using System.Data;
using Tool;

namespace SerialPortCommunicationProject
{
    public class Database
    {
        public Device[] RealListDevices = new Device[10];

        private Database()
        {
            conn = new NpgsqlConnection(connString);
        }

        private static readonly Database database = new Database();
        public static Database GetInstance()
        {
            return database;
        }

        private readonly string connString = "PORT=5432;HOST=127.0.0.1;PASSWORD=123456;USER ID=postgres";
        private readonly string DATABASENAME = "devicedebugtool";
        private const string DEVICETABLE = "device";
        private const string COMMANDTABLE = "command";
        private const string NORMALDEVICE = "normaldevice";

        private NpgsqlConnection conn = null;
        private void CloseConn()
        {
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }
        /// <summary>

        /// 测试连接PostGreSQL数据库

        /// </summary>

        /// <returns>Success/Failure</returns>

        public string TestConnection()

        {

            string strMessage = string.Empty;

            try

            {
                conn = new NpgsqlConnection(connString);

                conn.Open();

                strMessage = "Success";

                CloseConn();

            }

            catch (Exception ex)

            {
                strMessage = "Failure";

            }

            return strMessage;

        }

        public bool CreateDataBaseIfNotExists(string name)
        {
            return NormalCreateIfNotExists(null, tableName: name, "SELECT datname FROM pg_database WHERE datistemplate = false; ",
                 $@"CREATE DATABASE {name} WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default
                LC_COLLATE = 'Chinese (Simplified)_China.936' LC_CTYPE = 'Chinese (Simplified)_China.936' CONNECTION LIMIT = -1; ");
        }

        public bool CreateTableNamedDevice(string databaseName, string tableName)
        {
            return CreateTableIfNotExists(databaseName, tableName, $@"CREATE TABLE {databaseName}.public.{tableName}
                                                    (
                                                        name text NOT NULL,
                                                        commMode text default '串口', check(commMode in('串口','Socket(TCP)')),
                                                        encoding text default 'ASCII', check(encoding in('ASCII','HEX')),
                                                        portName text default '',
                                                        baudRates integer default 9600,
                                                        dataBits integer default 8,
                                                        parity text default 'None', check (parity in('None', 'Odd', 'Even', 'Mark', 'Space')),
                                                        stopBits text default '1', check (stopBits in('1', '1.5', '2')),
                                                        isServer BOOLEAN NOT NULL DEFAULT FALSE,
                                                        ip text default '127.0.0.1',
                                                        port integer default 0,
                                                        CONSTRAINT name PRIMARY KEY(name)
                                                    );");
        }

        public bool CreateTableNamedCommand(string databaseName, string tableName)
        {
            return CreateTableIfNotExists(databaseName, tableName, $@"CREATE TABLE {databaseName}.public.{tableName}
                        (
                          name text NOT NULL,
                          deviceName text NOT NULL,
                          code text NOT NULL,
                          CONSTRAINT command_pkey PRIMARY KEY (name, devicename),
                          CONSTRAINT command_devicename_fkey FOREIGN KEY (devicename)
                              REFERENCES public.device (name) MATCH SIMPLE
                              ON UPDATE NO ACTION ON DELETE NO ACTION
                        );");
        }

        public bool CreateTableIfNotExists(string databaseName, string tableName, string createSql)
        {
            return NormalCreateIfNotExists(databaseName, tableName,
                @"SELECT   tablename   FROM   pg_tables   
                                        WHERE   tablename   NOT   LIKE   'pg%'
                                        AND tablename NOT LIKE 'sql_%'
                                        ORDER   BY   tablename; ",
                                        createSql);
        }

        private bool NormalCreateIfNotExists(string databaseName, string tableName, string checkSql, string createSql)
        {
            bool isExisted = false;
            NpgsqlConnection conn = null;
            try
            {
                // connect to database you just created or has existed
                conn = new NpgsqlConnection(connString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                if (databaseName != null)
                {
                    conn.ChangeDatabase(databaseName.ToLower());
                }
                using (var cmd = new NpgsqlCommand(checkSql, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (reader.GetString(0).Equals(tableName, StringComparison.CurrentCultureIgnoreCase))
                            {
                                isExisted = true;
                                break;
                            }
                        }
                    }
                }

                if (!isExisted)
                {
                    using (var cmd = new NpgsqlCommand(createSql, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
                CloseConn();
            }
            catch (Exception ex)
            {
                CloseConn();
                return false;
            }
            return true;
        }

        //private void TryCatch()
        //{
        //    NpgsqlConnection conn = null;
        //    try
        //    {
        //        conn = new NpgsqlConnection(connString);
        //        conn.ChangeDatabase("devicedebugtool");
        //        if (conn.State != ConnectionState.Open)
        //        {
        //            conn.Open();
        //        }
        //        string sql = "DELETE FROM command WHERE devicename LIKE @p1 and name LIKE @p2";
        //        using (var cmd = new NpgsqlCommand(sql, conn))
        //        {
        //            cmd.Parameters.AddWithValue("p1", deviceName);
        //            cmd.Parameters.AddWithValue("p2", commandName);
        //            cmd.ExecuteNonQuery();
        //        }
        //        CloseConn();
        //    }
        //    catch (Exception ex)
        //    {
        //        CloseConn();
        //        return false;
        //    }

        //}

        /// <summary>
        /// 从数据库获取设备数据
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<Device> LoadAllDevices()
        {
            ObservableCollection<Device> lstDevices = new ObservableCollection<Device>();

            NpgsqlConnection conn = null;
            try
            {
                conn = new NpgsqlConnection(connString);
                conn.ChangeDatabase(DATABASENAME);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string deviceSql = @"SELECT name, commmode,encoding,portname,baudrates,databits,parity,stopbits,isserver,ip,port from device";
                using (var cmd = new NpgsqlCommand(deviceSql, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (reader.GetString(0) == "normaldevice")
                            {
                                continue;
                            }
                            Device device = new Device()
                            {
                                Name = reader.GetString(0),
                                CommMode = reader.GetString(1),
                                Encoding = reader.GetString(2),
                                Commands = new ObservableCollection<Command>()
                            };
                            if (device.CommMode == StaticData.SERIALPORT)
                            {
                                device.PortName = reader.GetString(3);
                                device.BaudRate = reader.GetInt32(4);
                                device.DataBits = reader.GetInt32(5);
                                device.Parity = App.StringToParity(reader.GetString(6));
                                device.StopBits = App.StringToStopBits(reader.GetString(7));
                                device.IsServer = false;
                                device.Ip = null;
                                device.Port = 0;
                            }
                            else if (device.CommMode == StaticData.SOCKETTCP)
                            {
                                device.PortName = null;
                                device.BaudRate = 0;
                                device.DataBits = 0;
                                device.Parity = 0;
                                device.StopBits = 0;
                                device.IsServer = reader.GetBoolean(8);
                                device.Ip = reader.GetString(9);
                                device.Port = reader.GetInt32(10);
                            }
                            lstDevices.Add(device);
                        }
                    }
                }
                foreach (Device d in lstDevices)
                {
                    string commandSql = @"SELECT devicename, name, code 
                                                                        from command
                                                                        where devicename=@p";
                    using (var cmd = new NpgsqlCommand(commandSql, conn))
                    {
                        cmd.Parameters.AddWithValue("p", d.Name);
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Command command = new Command()
                                {
                                    //DeviceName = reader.GetString(0),
                                    Name = reader.GetString(1),
                                    Code = reader.GetString(2)
                                };
                                d.Commands.Add(command);
                            }
                        }
                    }
                }
                CloseConn();

            }
            catch (Exception ex)
            {
                CloseConn();
                return null;
            }

            return lstDevices;
        }

        /// <summary>
        /// 删除指定名称的设置
        /// </summary>
        /// <param name="deviceName"></param>
        /// <returns></returns>
        public bool RemoveDevice(string deviceName)
        {
            NpgsqlConnection conn = null;
            try
            {
                conn = new NpgsqlConnection(connString);
                conn.ChangeDatabase(DATABASENAME);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string deleteDeviceSql = $"DELETE FROM device WHERE name like @p";
                using (var cmd = new NpgsqlCommand(deleteDeviceSql, conn))
                {
                    cmd.Parameters.AddWithValue("p", deviceName);
                    cmd.ExecuteNonQuery();
                }
                string deleteCommandSql = $"DELETE FROM command WHERE devicename like @p";
                using (var cmd = new NpgsqlCommand(deleteCommandSql, conn))
                {
                    cmd.Parameters.AddWithValue("p", deviceName);
                    cmd.ExecuteNonQuery();
                }
                CloseConn();
            }
            catch (Exception ex)
            {
                CloseConn();
                return false;
            }
            return true;
        }

        /// <summary>
        /// 删除指令设备的指定指令
        /// </summary>
        /// <param name="deviceName"></param>
        /// <param name="commandName"></param>
        /// <returns></returns>
        public bool RemoveCommand(string deviceName, string commandName)
        {
            NpgsqlConnection conn = null;
            try
            {
                conn = new NpgsqlConnection(connString);
                conn.ChangeDatabase("devicedebugtool");
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string sql = "DELETE FROM command WHERE devicename LIKE @p1 and name LIKE @p2";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("p1", deviceName);
                    cmd.Parameters.AddWithValue("p2", commandName);
                    cmd.ExecuteNonQuery();
                }
                CloseConn();
            }
            catch (Exception ex)
            {
                CloseConn();
                return false;
            }
            return true;
        }

        /// <summary>
        /// 获取指定名称的设备
        /// </summary>
        /// <param name="deviceName"></param>
        /// <returns></returns>
        public Device GetDevice(string deviceName)
        {
            Device device = null;
            NpgsqlConnection conn = null;
            try
            {
                conn = new NpgsqlConnection(connString);
                conn.ChangeDatabase("devicedebugtool");
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string deviceSql = "select name,commmode,encoding,portname,baudrates,databits,parity,stopbits,isserver,ip,port from device where name like @p;";
                using (var cmd = new NpgsqlCommand(deviceSql, conn))
                {
                    cmd.Parameters.AddWithValue("p", deviceName);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            device = new Device()
                            {
                                Name = reader.GetString(0),
                                CommMode = reader.GetString(1),
                                Encoding = reader.GetString(2),
                                Commands = new ObservableCollection<Command>()
                            };
                            if (device.CommMode == StaticData.SERIALPORT)
                            {
                                device.PortName = reader.GetString(3);
                                device.BaudRate = reader.GetInt32(4);
                                device.DataBits = reader.GetInt32(5);
                                device.Parity = App.StringToParity(reader.GetString(6));
                                device.StopBits = App.StringToStopBits(reader.GetString(7));
                                device.IsServer = false;
                                device.Ip = null;
                                device.Port = 0;
                            }
                            else if (device.CommMode == StaticData.SOCKETTCP)
                            {
                                device.PortName = null;
                                device.BaudRate = 0;
                                device.DataBits = 0;
                                device.Parity = 0;
                                device.StopBits = 0;
                                device.IsServer = reader.GetBoolean(8);
                                device.Ip = reader.GetString(9);
                                device.Port = reader.GetInt32(10);
                            }
                        }
                    }
                }

                if (device == null)
                {
                    return null;
                }

                string commandSql = @"SELECT devicename, name, code 
                                                                        from command
                                                                        where devicename=@p";
                using (var cmd = new NpgsqlCommand(commandSql, conn))
                {
                    cmd.Parameters.AddWithValue("p", device.Name);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Command command = new Command()
                            {
                                //DeviceName = reader.GetString(0),
                                Name = reader.GetString(1),
                                Code = reader.GetString(2)
                            };
                            device.Commands.Add(command);
                        }
                    }
                }
                CloseConn();
            }
            catch (Exception ex)
            {
                CloseConn();
                return null;
            }
            return device;
        }

        public Device GetNormalDevice()
        {
            return GetDevice("normaldevice");
        }

        public bool UpdateNormalDevice(Device device)

        {

            string sql = @"insert INTO public.device(
            name, commmode, encoding, portname, baudrates, databits, parity, 
            stopbits, isserver, ip, port)
            VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)
            on conflict(name) 
            DO UPDATE SET name=@p0, commmode = @p1, encoding =@p2,portname =@p3,baudrates =@p4,databits =@p5,
            parity =@p6,stopbits =@p7,isserver =@p8,ip =@p9,port =@p10";

            NpgsqlConnection conn = null;

            try

            {

                // connect to database you just created or has existed

                conn = new NpgsqlConnection(connString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                conn.ChangeDatabase("devicedebugtool");

                using (var cmd = new NpgsqlCommand(sql, conn))

                {
                    cmd.Parameters.AddWithValue("p0", NORMALDEVICE);
                    cmd.Parameters.AddWithValue("p1", device.CommMode);
                    cmd.Parameters.AddWithValue("p2", device.Encoding);
                    cmd.Parameters.AddWithValue("p3", device.PortName);
                    cmd.Parameters.AddWithValue("p4", device.BaudRate);
                    cmd.Parameters.AddWithValue("p5", device.DataBits);
                    cmd.Parameters.AddWithValue("p6", App.ParityToString(device.Parity));
                    cmd.Parameters.AddWithValue("p7", App.StopBitsToString(device.StopBits));
                    cmd.Parameters.AddWithValue("p8", device.IsServer);
                    cmd.Parameters.AddWithValue("p9", device.Ip);
                    cmd.Parameters.AddWithValue("p10", device.Port);
                    if (cmd.ExecuteNonQuery() < 1)
                    {
                        return false;
                    }

                }

                CloseConn();

            }

            catch (Exception ex)

            {

                CloseConn();

                return false;

            }
            return true;

        }



        public bool AddDevice(Device device)
        {
            string sql = @"insert INTO public.device(
            name, commmode, encoding, portname, baudrates, databits, parity, 
            stopbits, isserver, ip, port)
            VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)
            ON CONFLICT ON CONSTRAINT name DO NOTHING;";
            NpgsqlConnection conn = null;

            try

            {

                // connect to database you just created or has existed

                conn = new NpgsqlConnection(connString);

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                conn.ChangeDatabase("devicedebugtool");

                using (var cmd = new NpgsqlCommand(sql, conn))

                {
                    cmd.Parameters.AddWithValue("p0", device.Name);
                    cmd.Parameters.AddWithValue("p1", device.CommMode);
                    cmd.Parameters.AddWithValue("p2", device.Encoding);
                    cmd.Parameters.AddWithValue("p3", device.PortName);
                    cmd.Parameters.AddWithValue("p4", device.BaudRate);
                    cmd.Parameters.AddWithValue("p5", device.DataBits);
                    cmd.Parameters.AddWithValue("p6", App.ParityToString(device.Parity));
                    cmd.Parameters.AddWithValue("p7", App.StopBitsToString(device.StopBits));
                    cmd.Parameters.AddWithValue("p8", device.IsServer);
                    cmd.Parameters.AddWithValue("p9", device.Ip);
                    cmd.Parameters.AddWithValue("p10", device.Port);
                    if (cmd.ExecuteNonQuery() < 1)
                    {
                        return false;
                    }

                }

                foreach (Command c in device.Commands)
                {
                    if (c.Name.Equals("双击添加") || c.Name.Equals("+"))
                    {
                        break;
                    }
                    AddCommand(c.Name, device.Name, c.Code);
                }

                CloseConn();

            }

            catch (Exception ex)

            {

                CloseConn();

                return false;

            }



            return true;

        }



        public bool ModifyDevice(Device device, string newName)
        {
            // to modify
            string sql = @"delete from command where devicename=@p11;
            UPDATE public.device
            SET name=@p0, commmode = @p1, encoding =@p2,portname =@p3,baudrates =@p4,databits =@p5,
            parity =@p6,stopbits =@p7,isserver =@p8,ip =@p9,port =@p10
            WHERE name like @p11;";

            NpgsqlConnection conn = null;
            try

            {

                // connect to database you just created or has existed

                conn = new NpgsqlConnection(connString);

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                conn.ChangeDatabase("devicedebugtool");

                using (var cmd = new NpgsqlCommand(sql, conn))

                {
                    cmd.Parameters.AddWithValue("p0", newName);
                    cmd.Parameters.AddWithValue("p1", device.CommMode);
                    cmd.Parameters.AddWithValue("p2", device.Encoding);
                    cmd.Parameters.AddWithValue("p3", device.PortName);
                    cmd.Parameters.AddWithValue("p4", device.BaudRate);
                    cmd.Parameters.AddWithValue("p5", device.DataBits);
                    cmd.Parameters.AddWithValue("p6", App.ParityToString(device.Parity));
                    cmd.Parameters.AddWithValue("p7", App.StopBitsToString(device.StopBits));
                    cmd.Parameters.AddWithValue("p8", device.IsServer);
                    cmd.Parameters.AddWithValue("p9", device.Ip);
                    cmd.Parameters.AddWithValue("p10", device.Port);
                    cmd.Parameters.AddWithValue("p11", device.Name);
                    if (cmd.ExecuteNonQuery() < 1)
                    {
                        return false;
                    }

                }
                foreach (Command c in device.Commands)
                {
                    if (c.Name.Equals("双击添加") || c.Name.Equals("+"))
                    {
                        break;
                    }
                    AddCommand(c.Name, newName,c.Code);
                }


                CloseConn();

            }

            catch (Exception ex)

            {

                CloseConn();

                return false;

            }

            return true;

        }

        public bool AddCommand(string name, string deviceName, string code)
        {
            string sql = @"insert INTO public.command(
            name,devicename,code)
            VALUES (@p1,@p2,@p3)";
            NpgsqlConnection conn = null;

            try

            {
                // connect to database you just created or has existed

                conn = new NpgsqlConnection(connString);

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                conn.ChangeDatabase("devicedebugtool");

                using (var cmd = new NpgsqlCommand(sql, conn))

                {
                    cmd.Parameters.AddWithValue("p1", name);
                    cmd.Parameters.AddWithValue("p2", deviceName);
                    cmd.Parameters.AddWithValue("p3", code);
                    if (cmd.ExecuteNonQuery() < 1)
                    {
                        return false;
                    }

                }

                conn.Close();

            }

            catch (Exception ex)

            {

                CloseConn();

                return false;

            }

            return true;

        }
        public bool ModifyCommand(string deviceName, string commandName, string code)

        {

            string sql = @"UPDATE public.command
            SET  name=@p1, devicename=@p3, code =@p2
            WHERE name like @p1 and devicename like @p3; ";



            NpgsqlConnection conn = null;

            try

            {

                // connect to database you just created or has existed

                conn = new NpgsqlConnection(connString);

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                conn.ChangeDatabase("devicedebugtool");

                using (var cmd = new NpgsqlCommand(sql, conn))

                {
                    cmd.Parameters.AddWithValue("p1", commandName);
                    cmd.Parameters.AddWithValue("p2", code);
                    cmd.Parameters.AddWithValue("p3", deviceName);
                    if (cmd.ExecuteNonQuery() < 1)
                    {
                        return false;
                    }

                }

                CloseConn();

            }

            catch (Exception ex)

            {
                CloseConn();

                return false;

            }



            return true;

        }

    }

}